from odoo import models, fields, tools


class OrderReceivingAreaStatisticsReport(models.Model):
    _name = 's.order.receiving.area.statistics.report'
    _description = "Order Receiving Area Statistics Report"
    _auto = False

    date_index = fields.Integer(string='Date Index')
    row_index = fields.Integer(string='Row Index')
    order_type = fields.Selection([('0', 'WTT Self-Order'), ('1', 'JOVE Transfer Order')], string='Order Type')
    product_type = fields.Selection([('0', 'Negative'), ('1', 'Positive')], string='Product Type')
    order_date = fields.Date(string='Order Date')
    order_area = fields.Float(string='Order Area (m^2)', digits=(16, 2))

    def init(self):
        tools.drop_view_if_exists(self._cr, 's_order_receiving_area_statistics_report')
        query = """
        CREATE OR REPLACE VIEW s_order_receiving_area_statistics_report AS (
        -- 接单面积统计
SELECT
    row_number() over() as id,
    DENSE_RANK() OVER (ORDER BY SO.orderDate) AS date_index, -- 日期索引（用于生成Excel时的辅助列）
    CASE
        WHEN SO.orderType = 0 AND SO.productType = 1 THEN 1
        WHEN SO.orderType = 0 AND SO.productType = 0 THEN 2
        WHEN SO.orderType = 1 AND SO.productType = 1 THEN 4
        WHEN SO.orderType = 1 AND SO.productType = 0 THEN 5
        ELSE 0
    END AS row_index, -- 行索引（用于生成Excel时的辅助列）
    SO.orderType::character varying as order_type, -- 订单类型（0=WTT自接订单，1=JOVE转单订单）
    SO.productType::character varying as product_type, -- 流程（0=负片，1=正片）
    SO.orderDate as order_date, -- 接单日期
    ROUND(SUM(SO.orderArea)::numeric, 2) AS order_area -- 订单面积（平米）
FROM
    (
        SELECT
            CASE
                WHEN rp.code LIKE '250004%' THEN 1
                ELSE 0
            END AS orderType,
            CASE
                WHEN ptpl.value_id = 2960 THEN 1
                ELSE 0
            END AS productType,
            (so.create_date + INTERVAL '1 hour')::DATE AS orderDate, -- 返回的结果需要转回北京时间，所以小时加一
            so.s_product_metre AS orderArea
        FROM
            sale_order AS so
            LEFT JOIN res_partner AS rp ON rp.id = so.partner_id
            LEFT JOIN product_template_param_line AS ptpl ON ptpl.product_tmpl_id = so.product_id
                AND ptpl.attribute_id = 13961
        WHERE
            so.status != 'cancelled'
            AND so.state != 'cancel'
            AND so.create_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 hour' -- 获取本月的第一天，因为是以北京时间查询数据，所以小时减一
            AND so.create_date <= CONCAT(CURRENT_DATE - 1, ' 23:59:59.999')::TIMESTAMP - INTERVAL '1 hour' -- 获取昨天日期，因为是以北京时间查询数据，所以小时减一
    ) AS SO
GROUP BY
    SO.orderType,
    SO.productType,
    SO.orderDate
        )"""
        self._cr.execute(query)
